# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
import pymysql
from itemadapter import ItemAdapter
from books.items import AuthorItem, BookInfosItem, BookDetailsItem

'''存入MySQL数据库'''
class MySQLPipeline(object):
    def __init__(self):
        self.connect = pymysql.connect(
            host = '192.168.2.133',     # ip
            port = 3306,                 # 端口
            user = 'root',               # 用户名
            password = 'cjs122374',      # 密码
            database = 'flask_books',   # 连接数据库
            charset = 'utf8'             # 编码格式
        )
        self.cursor = self.connect.cursor()     # 执行命令游标

    def open_spider(self, spider):
        print('开启项目爬虫'.center(100,'*'))

    def process_item(self, item, spider):
        try:
            if isinstance(item, AuthorItem):   # 作者信息
                self.cursor.execute("insert into book_authors(author_name) values (%s)", list(dict(item).values()))

            if isinstance(item, BookInfosItem): # 书籍信息
                book_sql = "insert into book_infos(bid,cid,aid,did,sid,book_name,created_time,update_time,newest_name,book_desc,img_url,detail_url) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                self.cursor.execute(book_sql, list(dict(item).values()))

            if isinstance(item, BookDetailsItem): # 章节信息
                detail_sql = "insert into book_details(bid,did,sid,detail_title,detail_contents) values (%s, %s, %s, %s, %s)"
                self.cursor.execute(detail_sql, list(dict(item).values()))

            self.connect.commit()
        except Exception as e:
            print('插入数据有误：', e)
            self.connect.rollback()

        return item

    # 查询书籍是否存在
    def get_exists_book_name(self, b_name, sid, a_name):
        '''
        :param b_name: 书籍名称
        :param sid: 来源id
        :param a_name: 作者名称
        :return:
        '''
        sql = "SELECT bi.newest_name FROM book_infos bi INNER JOIN book_authors ba On bi.aid = ba.id WHERE bi.book_name = '{a}' and bi.sid = '{b}' and ba.author_name = '{c}';".format(a=b_name, b=sid, c=a_name)
        self.cursor.execute(sql)
        return self.cursor.fetchone()

    # 查询作者是否存在
    def get_exists_book_author(self, a_name):
        '''
        :param a_name: 作者名称
        :return:
        '''
        sql = "SELECT id FROM book_authors WHERE author_name = '{}';".format(a_name)
        self.cursor.execute(sql)
        return self.cursor.fetchone()

    # 查询书籍分类表
    def get_book_cate_data(self):
        sql = "SELECT id,cate_name FROM book_cate;"
        self.cursor.execute(sql)
        return self.cursor.fetchall()

    # 获取指定书籍章节id
    def get_appoit_book_detail_id(self, bid, sid):
        '''
        :param bid: 书籍id
        :param sid: 来源id
        :return:
        '''
        sql = "select did from book_details where bid = '{a}' and sid = '{b}' ORDER BY did;".format(a=bid, b=sid)
        self.cursor.execute(sql)
        return self.cursor.fetchall()

    # 更新书籍表信息
    def change_appoint_book_infos(self, did, utime, new_name, bid, sid, aid):
        '''
        :param did: 最新章节id
        :param utime: 最新更新时间
        :param new_name: 最新更新章节
        :param bid: 书籍id
        :param sid: 来源id
        :param aid: 作者id
        :return:
        '''
        update_sql = "UPDATE book_infos set did = '{a}', update_time = '{b}', newest_name = '{c}' WHERE bid = '{d}' and sid = '{e}' and aid = '{f}';".format(a=did, b=utime, c=new_name, d=bid, e=sid, f=aid)
        try:
            self.cursor.execute(update_sql)  # 执行修改sql命令
            self.connect.commit()
        except Exception as e:
            self.connect.rollback()
            print("修改书籍表信息错误：", e)
            return 412
        else:
            return 200

    def close_spider(self,spider):
        self.cursor.close()
        self.connect.close()
        print('结束项目爬虫'.center(100, '*'))
